﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using InvestmentIntelligence.DB;
using InvestmentIntelligence.DbModel.CustomModels;
using InvestmentIntelligence.DbModel.Models;

namespace InvestmentIntelligence.Data.Repository.SqlImplementation
{
    public class AnalyticsIdeaToTradeRepository : ISqlAnalyticsIdeaToTradeRepository
    {
        private const int MergeTimeout = 300;
        private const string TableName = "[Analytics].[IdeaToTrade]";

        public void Insert(IEnumerable<AnalyticsIdeaToTrade> items)
        {
            using (var conn = IIPConnectionString.CreateSqlConnection())
            {
                conn.Open();
                var bi = new BulkCopy<AnalyticsIdeaToTrade>(conn, null, TableName, items);
                bi.Execute();
            }
        }

        public void Merge(MergeArea area, IEnumerable<AnalyticsIdeaToTrade> items)
        {
            if (area.IdeaIds != null)
            {
                MergeOverIdeas(items, area.IdeaIds);
            }
            else
            {
                MergeOverFundBook(items, area.FundBookId);
            }
        }

        public void MergeOverFundBook(IEnumerable<AnalyticsIdeaToTrade> items, int fundBookId)
        {
            const string targetTableSearchCondition = "i.FundBookEntityId = @FundBook";
            var parameters = new[]
                                     {
                                         new KeyValuePair<string, object>("@FundBook", fundBookId), 
                                     };
            Merge(items, targetTableSearchCondition, parameters);
        }

        public void MergeOverIdeas(IEnumerable<AnalyticsIdeaToTrade> items, IEnumerable<Guid> ideaIds)
        {
            var targetTableSearchCondition = String.Format("i.[Id] IN ({0})", String.Join(",", ideaIds.Select(i => String.Format("'{0}'", i))));
            var parameters = new KeyValuePair<string, object>[0];
            Merge(items, targetTableSearchCondition, parameters);
        }

        private static void Merge(IEnumerable<AnalyticsIdeaToTrade> items, string serchCondition, IEnumerable<KeyValuePair<string, object>> parameters)
        {
            using (var conn = IIPConnectionString.CreateSqlConnection())
            {
                conn.Open();

                var tableName = DbHelper.BulkCopyToTempTable(conn, TableName, items);
                ExecuteMerge(conn, tableName, serchCondition, parameters);
            }
        }

        private static void ExecuteMerge(SqlConnection conn, string tempTable, string searchCondition, IEnumerable<KeyValuePair<string, object>> parameters)
        {
            const string mergePattern = @"
WITH targetArea AS (
    SELECT 
        itt.* 
    FROM
        [Analytics].[Idea] i
    INNER JOIN
        [Analytics].[IdeaToTrade] itt
    ON
        i.Id = itt.IdeaId
    WHERE
        {1}
)
MERGE
	targetArea itt
USING
	{0} ittTemp
ON
	itt.[IdeaId] = ittTemp.[IdeaId] AND itt.[TradeId] = ittTemp.[TradeId]
WHEN NOT MATCHED
	THEN INSERT VALUES ([IdeaId], [TradeId])
WHEN NOT MATCHED BY SOURCE
	THEN DELETE
OPTION (MAXDOP 1)
;
";
            var sql = string.Format(mergePattern, tempTable, searchCondition);
            var cmd = new SqlCommand(sql, conn) { CommandTimeout = MergeTimeout };
            foreach (var p in parameters)
            {
                cmd.Parameters.AddWithValue(p.Key, p.Value);
            }
            cmd.ExecuteNonQuery();
        }

    }
}
